set more off
local a 42
local y 2000

*** OUTCOMES FROM FLAT FILE FOR AGE 42

use "Data/NCDS/UKDA-5578-stata9_se/stata9_se/ncds6.dta", clear

rename ncdsid NCDSID

* merge RPI
ge year=`y'
merge m:1 year using scratchdata/RPI.dta, keep(match) nogenerate

* sex
drop sex
ge sex_`a'=dmsex

** Observation indicator
ge Obs_42=1

rename n6region region_`a'

********************************************************************************
* Part 1: pay period for employed
********************************************************************************

* gross
ge pp_gee=cgroprd
recode pp_gee (3=4) (4=4.33) (5=52) (6=.)

ge pp_geeo=cgropred
recode pp_geeo (4=4.33) (6=3) (7=5) (8=6) (9=7) (11=8.66) (12=6.495) (15=13) (18=0.2)
replace pp_geeo=. if pp_geeo>15

replace pp_gee=pp_geeo if pp_gee==.
drop pp_geeo 

* net
ge pp_nee=cnetprd 
recode pp_nee (3=4) (4=4.33) (5=52) (6=.)

ge pp_neeo=cnetpred
recode pp_neeo (4=4.33) (6=3) (7=5) (8=6) (9=7) (11=8.66) (12=6.495) (15=13) (18=0.2)
replace pp_neeo=. if pp_neeo>15

replace pp_nee=pp_neeo if pp_nee==.
drop pp_neeo

********************************************************************************
* Part 2: pay for employed
********************************************************************************

* gross
ge gpee_`a'=cgropay if cgropay>=0
recode gpee_`a' (9999998=.) (9999999=.) 

* net
ge npee_`a'=cnetpay  if cnetpay>=0
recode npee_`a' (9999998=.) (9999999=.) 

********************************************************************************
* Part 3: pay per week for employed
********************************************************************************

* employee: gross pay per week
replace gpee_`a'=gpee_`a'/pp_gee
drop pp_gee

* employee: net pay per week
replace npee_`a'=npee_`a'/pp_nee
drop pp_nee

********************************************************************************
* Part 4: hours per week for employed
********************************************************************************

* employed: usual hours of paid work per week
ge hoursee1=chours1 if chours1>=0 & chours1<99
ge hoursee2=chours2 if chours2>=0 & chours2<=168
ge hoursee3=chours3 if chours3>=0 & chours3<=168
egen  hoursee_`a'=rowtotal(hoursee*), missing
drop hoursee1-hoursee3

********************************************************************************
* Part 5: weekly earnings for self-employed
********************************************************************************

ge sy=seernsyr
recode sy (9998 9999=.)

ge sm=seernsmo
recode sm (98 99=.)

ge ey=seerneyr 
recode ey (9998 9999=.)

ge em=seernemo
recode em (98 99=.)

ge pp_ese=ey+(em-1)/12-(sy+(sm-1)/12)
replace pp_ese=1 if pp_ese==0
replace pp_ese=. if pp_ese<0
replace pp_ese=1 if pp_ese>1 & pp_ese!=.
drop ey em sy sm

ge ese_`a'=seearn  if seearn>=0
recode ese_`a' (99999999 99999998=.)

replace pp_ese=. if ese_`a'==.
replace pp_ese=1 if ese_`a'==0

replace ese_`a'=ese_`a'/(52*pp_ese)
drop pp_ese

********************************************************************************
* Part 6: hours per week for self-employed
********************************************************************************

ge hoursse_`a'=cjsehrs  if cjsehrs>=0 & cjsehrs<=168

********************************************************************************
* profit period for self-employed
********************************************************************************

ge sy=seprfsyr  
recode sy (9998 9999=.)

ge sm=seprfsmo
recode sm (98 99=.)

ge ey=seprfeyr
recode ey (9998 9999=.)

ge em=seprfemo
recode em (98 99=.)

ge pp_npse=ey+(em-1)/12-(sy+(sm-1)/12)
replace pp_npse=1 if pp_npse==0
replace pp_npse=. if pp_npse<0
replace pp_npse=1 if pp_npse>1 & pp_npse!=.
drop ey em sy sm

* self-employed: net profit within last 12 months
ge npse_`a'=seprofit if seprofit>=0
recode npse_`a' (99999999 99999998=.)
replace npse_`a'=0 if npse_`a'<10

replace pp_npse=. if npse_`a'==.
replace pp_npse=1 if npse_`a'==0

replace npse_`a'=npse_`a'/(52*pp_npse)
drop pp_npse

********************************************************************************
* Part 7: inflation adjustment
********************************************************************************

local vars npee_`a' gpee_`a' ese_`a' npse_`a'
foreach v of local vars {
replace `v'=`v'/rpi
}

********************************************************************************
* Part 8: Economic activity
********************************************************************************

rename econact EconAct
replace EconAct=. if EconAct==98|EconAct==99
replace EconAct=othacted if othacted<=4
replace EconAct=1 if othacted==12
replace EconAct=3 if othacted==13

ge EE_`a'=(EconAct==1|EconAct==2) if EconAct!=. 
ge SE_`a'=(EconAct==3|EconAct==4) if EconAct!=. 

* clasify as non-working if report zero earnings
replace EE_`a'=0 if npee_`a'==0 & EE_`a'!=.
replace SE_`a'=0 if ese_`a'==0  & SE_`a'!=.
ge AE_`a'=EE_`a'+SE_`a'

ge UN_`a'=EconAct==5 if EconAct!=.

********************************************************************************
* Part 9: recode hours
********************************************************************************

* recode hours to missing if hours>112 or zero
replace hoursee_`a'=. if (hoursee_`a'>112|hoursee_`a'==0)
replace hoursse_`a'=. if (hoursse_`a'>112|hoursse_`a'==0)

replace hoursee_`a'=40 if EconAct==1 & hoursee_`a'==.
replace hoursee_`a'=20 if EconAct==2 & hoursee_`a'==.
replace hoursse_`a'=40 if EconAct==3 & hoursse_`a'==.
replace hoursse_`a'=20 if EconAct==4 & hoursse_`a'==.

replace hoursee_`a'=. if  npee_`a'==.
replace hoursse_`a'=. if  ese_`a'==.

* recode earnings and hours as missing if don't match current economic activity
replace npee_`a'=. if EE_`a'!=1
replace gpee_`a'=. if EE_`a'!=1
replace hoursee_`a'=. if  EE_`a'!=1
replace ese_`a'=. if SE_`a'!=1
replace hoursse_`a'=. if SE_`a'!=1

********************************************************************************
* Part 10: hourly wages
********************************************************************************

* employed net
ge wageee_`a'=npee_`a'/hoursee_`a'

* employed gross
ge gwageee_`a'=gpee_`a'/hoursee_`a'

* self-employed (earnings)
ge wagese_`a'=ese_`a'/hoursse_`a'

* self-employed (profit) 
ge prose_`a'=npse_`a'/hoursse_`a'

********************************************************************************
* Part 11: combine employed and self-employed
********************************************************************************

egen wage_`a'=rowmax(wageee_`a' wagese_`a')
drop wageee_`a' wagese_`a'

egen pay_`a'=rowmax(npee_`a' ese_`a')
drop npee_`a' ese_`a'

egen hours_`a'=rowmax(hoursee_`a' hoursse_`a')
drop hoursee_`a' hoursse_`a'

********************************************************************************
*  Part 12: log variables
********************************************************************************

local vars wage pay hours
foreach v of local vars {
ge l`v'_`a'=ln(`v'_`a')
}

********************************************************************************
* Part 13: SOC 1990
********************************************************************************

ge soc90_`a'=soc
replace soc90_`a'=. if AE_`a'!=1

********************************************************************************
* Part 14: Occupational attainment
********************************************************************************

rename sc SC_`a'
replace SC_`a'=. if SC_`a'==6|AE_`a'!=1


********************************************************************************
* Part 15: Job security
********************************************************************************

rename jsecure  JobSecurity_`a'
replace JobSecurity_`a'=. if JobSecurity_`a'==9|AE_`a'!=1
ge JobSecurityHi_`a'=JobSecurity_`a'==1 if JobSecurity_`a'!=.
ge JobSecurityMedHi_`a'=JobSecurity_`a'==1|JobSecurity_`a'==2 if JobSecurity_`a'!=.

********************************************************************************
* Part 16: Industry
********************************************************************************

ge j=sic
destring sic, gen(Ind_`a')  /* 1992 code */
replace Ind_`a'=. if Ind_`a'<0|AE_`a'!=1
replace  Ind_`a'= Ind_`a'*100

*two digit sic 1992
gen twodigit=int(Ind_`a'/100)

ge GL="A" if twodigit<=02 						/* Agriculture And Forestry */
replace GL="B" if twodigit==05  				/* Fishing */
replace GL="C" if twodigit>=10 & twodigit<=14 	/* Mining And Quarrying*/
replace GL="D" if twodigit>=15 & twodigit<=37  	/* Manufacturing */
replace GL="E" if twodigit>=40 & twodigit<=41  	/* Electricity, Gas And Water Supply */
replace GL="F" if twodigit==45  				/* Construstion */
replace GL="G" if twodigit>=50 & twodigit<=52  	/* Wholesale And Retail Trade; Repair Of Motor Vehicles, Motorcycles And Personal and Household Goods */
replace GL="H" if twodigit==55  				/* Hotels And Restaurants */
replace GL="I" if twodigit>=60 & twodigit<=64  	/* Transport, Storage And Communication  */
replace GL="J" if twodigit>=65 & twodigit<=67  	/* Financial Activities */
replace GL="K" if twodigit>=70 & twodigit<=74  	/* Property Development, Renting, Business And Research Activities */
replace GL="L" if twodigit==75  				/* Public Administration And Defence; Social Security */
replace GL="M" if twodigit==80 					/* Education */
replace GL="N" if twodigit==85 					/* Health And Social Work */
replace GL="O" if twodigit>=90 & twodigit<=93  	/* Other Community, Social And Personal Service Activities */
replace GL="P" if twodigit==95  				/* Private Households With Employed Persons */
replace GL="Q" if twodigit==99 					/* International Organisations And Bodies */

/* SIC=89 is not defined - 110 observations*/
rename GL GL_`a'
encode GL_`a', gen(gl_`a')

********************************************************************************
* Education
********************************************************************************

ge HiQual_`a'=0 if edquals2==1|edquals2==2

replace HiQual_`a'=1 if edscot1==1  & edscotya<=1981
replace HiQual_`a'=1 if edcse2>0 & edcse2<98 & cseyrq<=1981
replace HiQual_`a'=2 if edolev1>0 & edcse1<98 & olvlyrq<=1981
replace HiQual_`a'=2 if edcse1>0 & edcse1<98 & cseyrq<=1981
replace HiQual_`a'=2 if edscot1==2  & edscotyb<=1981
replace HiQual_`a'=3 if edscot1==4  & edscotyd<=1981
replace HiQual_`a'=3 if edgcasl1>0 & edgcasl1<98  & edyear56<=1981
replace HiQual_`a'=3 if edgcasl2>0 & edgcasl2<98  & edyear56<=1981


* Degree (5)
foreach i of numlist 66(1)69 81(1)83{
ge Iedyear`i'=edyear`i'<=1981 if edyear`i'!=.
}
egen Degree_`a'=rowtotal(Iedyear*)
egen nm=rownonmiss(Iedyear*)
drop Iedyear*
replace Degree_`a'=. if nm==0
replace Degree_`a'=1 if  Degree_`a'>1 &  Degree_`a'!=.
drop nm

replace HiQual_`a'=5 if Degree_`a'==1

* HQual (4)
rename edyear* edyea*
rename vocyear vocyear1
rename vocyear* vocyea* 
foreach i of numlist 86 101(1)102{
ge Iedyear`i'=edyea`i'<=1981 if edyea`i'!=.
}
foreach i of numlist 1(1)8 74(1)75{
ge Iedyear`i'=vocyea`i'<=1981 if vocyea`i'!=.
}

egen HQual_`a'=rowtotal(Iedyear*)
egen nm=rownonmiss(Iedyear*)
drop Iedyear*
replace HQual_`a'=. if nm==0
replace HQual_`a'=1 if  HQual_`a'>1 &  HQual_`a'!=.
replace HQual_`a'=1 if eddipwhn<=1981
replace HQual_`a'=0 if eddipwhn>1981 & eddipwhn!=. & HQual_`a'==.
drop nm




*Voc2
foreach i of numlist  16(1)46 65(1)71{
ge Iedyear`i'=vocyea`i'<=1981 if vocyea`i'!=.
}
egen Voc2_`a'=rowtotal(Iedyear*)
egen nm=rownonmiss(Iedyear*)
drop Iedyear*
replace Voc2_`a'=. if nm==0
replace Voc2_`a'=1 if  Voc2_`a'>1 &  Voc2_`a'!=.
drop nm


* Age left school
rename  actagel2  agelsch_`a'
replace agelsch_`a'=16 if agelsch_`a'<16
replace agelsch_`a'=18 if agelsch_`a'>18 & agelsch_`a'!=.

********************************************************************************
* Finish up
*******************************************************************************

rename skill* skill*_`a'

keep NCDSID *_`a'
sort NCDSID
rename *_`a' *_I`a'

save scratchdata/ncds_2000_flatfile.dta, replace

